package service;

import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import bean.Linkman;
import base.Service;

public class LinkmanS extends Service
{
	public int getCount(String columnName,String keyword)
	{
		String sql="select count(*) "+
                   "from linkman left outer join corporation on lcornumber=cnumber "+
                   "where lisDel=0 ";
        if(!"".equals(columnName)&&!"".equals(keyword))
           sql+=" and "+columnName+" like '%"+keyword+"%'";
        logger.debug(sql);
        return jdbc.queryForInt(sql);		
	}
	public List getPage(int amount,int pageSize,int pageNum,String columnName,String keyword)
	{
		String sql="select * " +
				   "from(select top "+(pageSize*pageNum>amount?(amount-pageSize*(pageNum-1)):pageSize)+ " * "+
		               "from (select top "+pageSize*pageNum+" lid,lname,cname,ltel,lfax,lmobile,cnumber "+
		                     "from linkman left outer join corporation on lcornumber=cnumber "+
		                     "where lisDel=0 ";
        if(!"".equals(columnName)&&!"".equals(keyword))
	                        sql+=" and "+columnName+" like '%"+keyword+"%' ";
                        sql+="order by cnumber desc) temp1 "+
                       "order by cnumber asc) temp2 "+
                   "order by cnumber desc";
        logger.debug(sql);
        return jdbc.queryForList(sql);
	}
	public Map getLinkMan(int id)
	{
		String sql="select * "+
                   "from linkman linkman left outer join corporation on lcornumber=cnumber "+
                   "where lid="+id;
       // System.out.println(sql);
        return jdbc.queryForMap(sql);
	}
	public List getCorLinkMans(String corNumber)
	{
	     String sql="select * from linkman where lcorNumber='"+corNumber+"'";
	    // System.out.println(sql);
	     return jdbc.queryForList(sql);
	}
	
	public void save(Linkman linkman)
	{
		 String sql="";
   	     if(0!=linkman.getId())
   	     {
   		    sql="update linkman set lname='"+linkman.getName()+"',"+
                               "lposition='"+linkman.getPosition()+"',"+
                                    "ltel='"+linkman.getTel()+"',"+
                                    "lfax='"+linkman.getFax()+"',"+
                                 "lmobile='"+linkman.getMobile()+"',"+
                                  "lmail='"+linkman.getEmail()+"',"+
                                     "lqq='"+linkman.getQq()+"',"+
                                    "lmsn='"+linkman.getMsn()+"',"+
                                "lcountry='"+linkman.getCountry()+"',"+
                               "lprovince='"+linkman.getProvince()+"',"+
                                   "lcity='"+linkman.getCity()+"',"+
                                 "lcounty='"+linkman.getCounty()+"',"+
                                "laddress='"+linkman.getAddress()+"',"+
                                 "lremark='"+linkman.getRemark()+"',"+
                           "leditEmployee='"+linkman.getEditEmployee()+"',"+
                               "leditDate='"+linkman.getEditDate()+"' "+
   			    "where lid="+linkman.getId();
   	     }
   	     else
   	     {  
   	    	                         
   		    sql="insert into linkman(lcorNumber,lname,lposition,ltel,lfax,lmobile,lmail,lqq,lmsn,lcountry,lprovince,lcity,lcounty,laddress,lremark,lappendEmployee,lappendDate,leditEmployee,leditDate,lisDel) values("+
                      		     "'"+linkman.getCorNumber()+"',"+                  
   		                         "'"+linkman.getName()+"',"+
   		                         "'"+linkman.getPosition()+"',"+
   		                         "'"+linkman.getTel()+"',"+
   		                         "'"+linkman.getFax()+"',"+
   		                         "'"+linkman.getMobile()+"',"+
   		                         "'"+linkman.getEmail()+"',"+
   		                         "'"+linkman.getQq()+"',"+
                                 "'"+linkman.getMsn()+"',"+
                                 "'"+linkman.getCountry()+"',"+
                                 "'"+linkman.getProvince()+"',"+
                                 "'"+linkman.getCity()+"',"+
                                 "'"+linkman.getCounty()+"',"+
                                 "'"+linkman.getAddress()+"',"+
                                 "'"+linkman.getRemark()+"',"+
                                 "'"+linkman.getAppendEmployee()+"',"+
                                 "'"+linkman.getAppendDate()+"',"+
                                 "'"+linkman.getEditEmployee()+"',"+
                                 "'"+linkman.getEditDate()+"',0)";
   	     }
   	     //System.out.println(sql);
   	     jdbc.execute(sql);
	}
	public void delLinkMan(int id)
	{
		String sql="update linkman set lisDel=1 where lid="+id;
   	    jdbc.execute(sql);	
	}
}
